version 14
clear
set more off
set linesize 157

global orig "../orig"    // folder of the basic data
global data "../data"    // folder of the generated data
global prog "../prog"    // folder of the do-files
global log  "../log"     // folder of the log-files

do ${prog}/00_generate_SIAB_panel.do
do ${prog}/01a_SIAB
do ${prog}/01b_control_vars
do ${prog}/02_robots
do ${prog}/03_Trade
do ${prog}/04_ICT
do ${prog}/05_merge_datasets


/*
The following script generates several variables on the Kreis, year level.

This consists of 4 datasources:


*** 0. SIAB ***
Based on an IAB script, we create a yearly panel of individuals (do-file 00, 00a, 00b).


*** 01a. SIAB ***
Those are then aggregated to Kreis, year level.
Here we create different labor market indicators by aggregating individuals.
The number of underlying individuals in each cell is displayed in "n_individuals"
or in the respective variable following each variable, always starting with n_...
Note that all employment variables are full-time equivalent, ie. part time is counted as 0.5.
This leads to Nachkommastellen in the aggregation.
All variables from this step start with SIAB_...

*** 01b. SIAB-BHP ***
This script uses the firm dataset adjacent to the IAB (SIAB_7517_v1_bhp_basis_v1).
Here we create a set of variables based on all unique firms appearing between 1984 and 1994.

*** 1.1. Baseline control
First, based on the number of employees of those firms, we calculate employment
by broad sector . Here the calculation is only based on full-time employees.
Thus, the number of underlying individuals is equivalent to the count and we do
not report the number again.
The variables are the aggretagte of the 1984-1994 period and we merge them to the
main dataset only once such that we only have values for 1994.
All variables from this step start with base_...

*** 2. International Federation of Robotics (IFR) ***
The IFR provides information on operating robots in Germany and elsewhere on a
year, industry level, mostly 2-digit WZ08 level, some on 3-digit WZ08 level.
We calculate robots per Kreis, year by using the methodology of Graetz and
Michaels (2018) which has been previously applied to IAB data by Dauth et al.
(workingpaper).

We first calculate employment by region, sector and year (employment_r_s_y) and
calculate the overall number of employees in a given sector (employment_s_y)
using the SIAB panel (step 1.2 of script 01b).


Then we distribute each sector's robots over different Kreise by the share of
employees of a sector working in a given Kreis (employment_r_s_y/employment_s_y).
Finally we add up over different sectors to reach a grand total of robots per
Kreis and year.
We do this seperately for 2-digit industries (step 2.2) and 3-digit industries (step 2.3).
These two are then summed up (step 2.4)

Variables and description
2A robot_count: estimate of the number of robots based on German data
2B robot_eu_count: estimate of the number of robots based on non-German EU data
2C robot_intens: robots count, normalized by employees  based on German data (var: 1A emp)
2D robot_eu_intens: robots count, normalized by employees based on non-German EU data (var: 1A emp)
2E n_robots: number of establishments underlying the aggregation
2F robot_count_2d: estimate of the number of robots based on German data, only using 2-digit industries
2G robot_eu_count_2d: estimate of the number of robots based on non-German EU data, only using 2-digit industries
2H robot_intens_2d: robots count, normalized by employees  based on German data (var: 1A emp), only using 2-digit industries
2I robot_eu_intens_2d: robots count, normalized by employees based on non-German EU data (var: 1A emp), only using 2-digit industries
2J n_robots_2d: number of establishments underlying the aggregation, only using 2-digit industries


All aggregated to year, Kreis level




*** 3. COMTRADE ***
This is data downloaded from UN COMTRADE on imports and exports of goods between Germany and China or Eastern Europe.
The data is on a classified by goods: SITC. We crosswalk this to industry-classifications (NACErev1 2-digit = WZ93)
using an official crosswalk from the UN.
Some good belong to several industries. We use employment shares of of the
industries between 1980 and 1990 to allocate the goods to the industries. The employment shares are calculated using the BHP
by agggregating all firms of the 1980 to 1990 waves to the 3-digit level
of WZ93_3_gen (step 3.1).
Once we have the exports and imports per sector, we distribute them over Kreise in the same way as we did with the
robots data. For each year, the share of a sector's trade allocated to a given Kreis is proportional to its share
of sector-specific employment relative to total employment in the sector in that year.

Variables and description
3A China_Export
3B China_Import
3C Eastern_Europe_Export
3D Eastern_Europe_Import
3E n_trade: number of establishments underlying the agggregation
All aggregated to year, Kreis level




*** 4. EUKLEMS ***
We obtained ICT capital stocks per sector, year from the EUKLEMS website. EUKLEMS is a database which gathers
data from Eurostat and national statistics offices.
We use data from ICT capital stocks in Germany and values from the US which we want to use as an instrument.
Again, we calculate ICT capital stocks per Kreis by using the employment shares of industries in different Kreise.


Variables and description
4A ICT_k: avg. ICT capital stock per worker, based on German ICT data
4B ICT_us_k: avg. ICT capital stock per worker, based on US data
4C ICT_us_k: avg. ICT capital stock per worker, based on EU (non-DE) data
4D ICT_total_k: total ICT capital stock, based on German ICT data
4E ICT_us_total_k: total ICT capital stock, based on US data
4E ICT_eu_total_k: total ICT capital stock, based on EU (non-DE) data
4G n_ICT: number of establishments underlying the agggregation

All aggregated to year, Kreis level

*** 5. Merge all datasets ***
Here, we merge all datasets.
We delete cells from the BHP containing less than 20 employees.
We confirm that all other aggregations are based on at least 20
establishments.

The dataset we export is called "aggregated_sample.dta".










